/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.session;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.slf4j.LoggerFactory;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import com.thienloc.omap.entity.TblRawMo;
import com.thienloc.omap.entity.report.MoStatistic;
import java.math.BigDecimal;
import java.util.*;

/**
 *
 * @author GiangLT
 */
@Stateless
public class TblRawMoFacade extends AbstractFacade<TblRawMo> {

    @PersistenceContext(unitName = "omapPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public TblRawMoFacade() {
        super(TblRawMo.class);
    }

    public Set<String> findMdnByContentList(List<String> contents, Date startDate, Date endDate, String shortCode) throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("TblRawMo.findMdnByContentList", String.class);
            q.setParameter("contents", contents);
            q.setParameter("startDate", startDate);
            q.setParameter("endDate", endDate);
            q.setParameter("shortCode", shortCode);
            Set<String> result = new TreeSet<String>();
            Collections.addAll(result, q.getResultList().toArray(new String[0]));
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            LoggerFactory.getLogger(TblRawMtFacade.class.getName()).error("", ex);
            return null;
        }
    }

    public List<MoStatistic> findByContentList(List<String> contents, List<String> shortCodes, List<String> dates) {
        try {
            StringBuilder sb = new StringBuilder("SELECT m.sc, m.content, COUNT(m.content) AS total FROM Tbl_Raw_Mo m ");
            sb.append(" WHERE  m.content IN (");
            for (String content : contents) {
                sb.append("'").append(content).append("',");
            }
            sb.deleteCharAt(sb.length() - 1);

            sb.append(") AND  m.sc IN (");
            for (String shortCode : shortCodes) {
                sb.append("'").append(shortCode).append("',");
            }
            sb.deleteCharAt(sb.length() - 1);

            sb.append(") AND TRUNC(m.time) IN (");
            for (String date : dates) {
                sb.append("'").append(date).append("',");
            }
            sb.deleteCharAt(sb.length() - 1);

            sb.append(") GROUP BY  m.sc, m.content ORDER BY m.sc, total DESC");

            Query q = em.createNativeQuery(sb.toString());
            List rows = q.getResultList();

            List<MoStatistic> moStatistics = new ArrayList<MoStatistic>();
            for (Object row : rows) {
                Object[] fields = (Object[]) row;
                MoStatistic moStatistic = new MoStatistic();
                moStatistic.setShortCode((String) fields[0]);
                moStatistic.setContent((String) fields[1]);
                moStatistic.setCount((BigDecimal) fields[2]);

                moStatistics.add(moStatistic);
            }


//            String queryString = "SELECT m.sc, m.content, COUNT(m.content) AS total FROM TblRawMo m "
//                    + " WHERE m.content IN :contents AND m.sc IN :shortCodes AND m.time BETWEEN :startDate AND :endDate"
//                    + " GROUP BY  m.sc, m.content"
//                    + " ORDER BY m.sc, total DESC";
//
//            for (Date date : dates) {
//                Calendar cal = Calendar.getInstance();
//                cal.setTime(date);
//                cal.set(Calendar.HOUR_OF_DAY, 0);
//                cal.set(Calendar.MINUTE, 0);
//                cal.set(Calendar.SECOND, 0);
//                Date startDate = cal.getTime();
//
//                cal.set(Calendar.HOUR_OF_DAY, 23);
//                cal.set(Calendar.MINUTE, 59);
//                cal.set(Calendar.SECOND, 59);
//                Date endDate = cal.getTime();
//
//                Query q = em.createQuery(queryString);
//                q.setParameter("contents", contents);
//                q.setParameter("shortCodes", shortCodes);
//                q.setParameter("startDate", startDate);
//                q.setParameter("endDate", endDate);
//
//                List rows = q.getResultList();
//
//                for (Object row : rows) {
//                    Object[] fields = (Object[]) row;
//                    MoStatistic moStatistic = new MoStatistic();
//                    moStatistic.setShortCode((String) fields[0]);
//                    moStatistic.setContent((String) fields[1]);
//                    moStatistic.setCount((Long) fields[2]);
//
//                    moStatistics.add(moStatistic);
//                }
//
//            }
            return moStatistics;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            LoggerFactory.getLogger(TblRawMoFacade.class.getSimpleName()).error("", ex);
            return null;
        }
    }

    public List<String> findSubscriberList(String content, String shortCode, List<String> dates) {
        try {
            StringBuilder sb = new StringBuilder("SELECT DISTINCT(m.msisdn) FROM Tbl_Raw_Mo m ");
            sb.append(" WHERE m.content = ?1 AND m.sc = ?2 AND TRUNC(m.time) IN (");
            for (String date : dates) {
                sb.append("'").append(date).append("',");
            }
            sb.deleteCharAt(sb.length() - 1);

            sb.append(") ORDER BY m.msisdn");

            Query q = em.createNativeQuery(sb.toString());
            q.setParameter(1, content);
            q.setParameter(2, shortCode);
            List rows = q.getResultList();
            List<String> result = new ArrayList<String>(rows.size());
            for (Object object : rows) {
                result.add((String) object);
            }
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            LoggerFactory.getLogger(TblRawMoFacade.class.getSimpleName()).error("", ex);
            return null;
        }
    }
}
